{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对一个表增加一列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 授权确认字段\n",
    "ALTER TABLE course_access\n",
    "ADD COLUMN access_granted BOOLEAN NOT NULL DEFAULT FALSE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "-- 外键(Reference)字段\n",
    "ALTER TABLE `users`\n",
    "ADD COLUMN `role_id` INT;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "增加外键之后需要手动设立这个关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "ALTER TABLE `users`\n",
    "ADD CONSTRAINT `fk_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "设置列的默认值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "ALTER TABLE users\n",
    "MODIFY role_id INT DEFAULT 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "检查表结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "DESCRIBE course_access;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们尝试设计一些单数据库的多表格的设计。首先，只有一个数据库，**数据库：user_management**。\n",
    "表名：`users`\n",
    "\n",
    "| 字段名     | 数据类型     | 约束                        | 说明               |\n",
    "| ---------- | ------------ | --------------------------- | ------------------ |\n",
    "| id         | INT          | PRIMARY KEY, AUTO_INCREMENT | 用户的唯一标识     |\n",
    "| username   | VARCHAR(50)  | UNIQUE, NOT NULL            | 用户名，必须唯一   |\n",
    "| email      | VARCHAR(100) | UNIQUE, NOT NULL            | 用户邮箱，必须唯一 |\n",
    "| password   | VARCHAR(255) | NOT NULL                    | 加密存储的用户密码 |\n",
    "| created_at | TIMESTAMP    | DEFAULT CURRENT_TIMESTAMP   | 用户创建时间       |\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "CREATE DATABASE IF NOT EXISTS user_management;\n",
    "\n",
    "USE user_management;\n",
    "\n",
    "CREATE TABLE users (\n",
    "    id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "    username VARCHAR(50) UNIQUE NOT NULL,\n",
    "    email VARCHAR(100) UNIQUE NOT NULL,\n",
    "    password VARCHAR(255) NOT NULL,\n",
    "    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n",
    ");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Course 课程表\n",
    "**课程表 (courses)**:\n",
    "\n",
    "- `id` (int, primary key)\n",
    "- `title` (varchar, course title)\n",
    "- `description` (text, course description)\n",
    "- `price` (decimal, course price)\n",
    "- `is_free` (boolean, is the course free)\n",
    "- `created_at` (datetime, timestamp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**章节表 (modules)**:\n",
    "\n",
    "- `id` (int, primary key)\n",
    "- `course_id` (foreign key, references courses.id)\n",
    "- `module_title` (varchar, chapter title)\n",
    "- `module_order` (int, order of the module in the course)\n",
    "- `created_at` (datetime, timestamp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**内容表 (contents)**:\n",
    "\n",
    "- `id` (int, primary key)\n",
    "- `module_id` (foreign key, references modules.id)\n",
    "- `content_type` (enum: `video`, `document`, etc.)\n",
    "- `content_url` (varchar, path to content)\n",
    "- `content_order` (int, order of content in the module)\n",
    "- `created_at` (datetime, timestamp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**用户表 (users)**:\n",
    "\n",
    "- `id` (int, primary key)\n",
    "- `username` (varchar, unique)\n",
    "- `email` (varchar, unique)\n",
    "- `password` (hashed varchar, user password)\n",
    "- `created_at` (datetime, timestamp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**用户课程权限表 (course_access)**:\n",
    "\n",
    "- `id` (int, primary key)\n",
    "- `user_id` (foreign key, references users.id)\n",
    "- `course_id` (foreign key, references courses.id)\n",
    "- `access_granted` (boolean, default: `false`)\n",
    "- `granted_at` (datetime, timestamp)"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
